const db = require("../config/mysql");
const dayjs = require('dayjs');

class InventoryModel {
  /** 列表+分页+搜索 */
  constructor() {
    this.db = db;
  }
  // 出库列表查询
  async getInventory(params) {
    // 确保参数转换为正确类型并设置默认值
    const page = parseInt(params.page) || 1;
    const page_size = parseInt(params.page_size) || 10;
    const goods_id = params.goods_id || '';
    const house_id = params.house_id || '';
    const status = params.status || 0;

    // 计算偏移量，确保页码有效
    const offset = Math.max(0, (page - 1) * page_size);

    // 分页查询搜索编号入库时间供应商
    let sql = 'SELECT stock_id,goods_id,house_id,current_stock,safety_stock,updated_at,status,notes FROM inventory WHERE 1=1';
    const queryParams = [];

    // 精准查询
    if (goods_id) {
      sql += ' AND goods_id = ?';
      queryParams.push(goods_id);
    }
    if (house_id) {
      sql += ' AND house_id = ?';
      queryParams.push(house_id);
    }
    if (status) {
      sql += ' AND status = ?';
      queryParams.push(status);
    }

    // 先查总条数
    let countSql = 'SELECT COUNT(*) as total FROM inventory WHERE 1=1';
    const countParams = [];
    if (goods_id) {
      countSql += ' AND goods_id = ?';
      countParams.push(goods_id);
    }
    if (house_id) {
      countSql += ' AND house_id = ?';
      countParams.push(house_id);
    }
    if (status) {
      countSql += ' AND status = ?';
      countParams.push(status);
    }

    // 添加排序和分页（移除对不存在的id列的引用）
    sql += ' ORDER BY updated_at DESC LIMIT ? OFFSET ?';
    queryParams.push(page_size, offset);

    try {
      const [rows] = await this.db.query(sql, queryParams);
      const [countResult] = await this.db.query(countSql, countParams);
      const total = countResult[0].total;

      // 获取所有需要的商品ID和仓库ID
      const goodsIds = rows.map(row => row.goods_id).filter((id, index, self) => id && self.indexOf(id) === index);
      const houseIds = rows.map(row => row.house_id).filter((id, index, self) => id && self.indexOf(id) === index);

      // 根据库存记录中的商品ID查询商品信息
      let goodsSql = 'SELECT * FROM goods WHERE 1=1';
      let houseSql = 'SELECT * FROM house WHERE 1=1';
      let house_areaSql = 'SELECT * FROM house_area WHERE 1=1';
      const goodsParams = [];
      const houseParams = [];
      const houseAreaParams = [];

      // 查询商品信息
      if (goodsIds.length > 0) {
        goodsSql += ' AND goods_id IN (?)';
        goodsParams.push(goodsIds);
      }

      // 查询仓库信息
      if (houseIds.length > 0) {
        houseSql += ' AND house_id IN (?)';
        houseParams.push(houseIds);

        // 同时查询这些仓库下的所有库区
        house_areaSql += ' AND house_id IN (?)';
        houseAreaParams.push(houseIds);
      }

      // 执行查询
      // 修改查询语句，为name字段添加别名避免冲突
      goodsSql = goodsSql.replace('SELECT *', 'SELECT *, name AS goods_name, code AS goods_code');
      houseSql = houseSql.replace('SELECT *', 'SELECT *, name AS house_name, code AS house_code');
      house_areaSql = house_areaSql.replace('SELECT *', 'SELECT *, area_name AS house_area_name, code AS house_area_code');

      // 准备查询入库子表的expiration_date字段
      let inventoryInitItemSql = 'SELECT goods_id, MIN(expiration_date) AS earliest_expiration_date, MAX(expiration_date) AS latest_expiration_date FROM inventoryinitem WHERE 1=1';
      const inventoryInitItemParams = [];

      // 根据goodsIds筛选
      if (goodsIds.length > 0) {
        inventoryInitItemSql += ' AND goods_id IN (?)';
        inventoryInitItemParams.push(goodsIds);
      }

      // 按goods_id分组
      inventoryInitItemSql += ' GROUP BY goods_id';

      // 执行所有查询
      const [goodsRows] = await this.db.query(goodsSql, goodsParams);
      const [houseRows] = await this.db.query(houseSql, houseParams);
      const [houseAreaRows] = await this.db.query(house_areaSql, houseAreaParams);
      const [inventoryInitItemRows] = await this.db.query(inventoryInitItemSql, inventoryInitItemParams);

      // 将查询结果转换为以ID为键的对象，便于查找
      const goodsMap = {};
      goodsRows.forEach(goods => {
        goodsMap[goods.goods_id] = goods;
      });

      const houseMap = {};
      houseRows.forEach(house => {
        houseMap[house.house_id] = house;
      });

      // 将库区信息按仓库ID分组
      const houseAreaMap = {};
      houseAreaRows.forEach(area => {
        if (!houseAreaMap[area.house_id]) {
          houseAreaMap[area.house_id] = [];
        }
        houseAreaMap[area.house_id].push(area);
      });

      // 创建入库子表信息的映射，便于查找
      const inventoryInitItemMap = {};
      inventoryInitItemRows.forEach(item => {
        inventoryInitItemMap[item.goods_id] = item;
      });

      // 合并库存记录、商品信息、仓库信息、库区信息和入库子表信息
      // 确保safety_stock字段不会被覆盖，放在展开运算后明确指定
      const mergedList = rows.map(row => ({
        ...(goodsMap[row.goods_id] || {}),
        ...(houseMap[row.house_id] || {}),
        ...(inventoryInitItemMap[row.goods_id] || {}), // 合并入库子表信息
        ...row, // 库存记录放在后面，确保核心字段不会被覆盖
        safety_stock: row.safety_stock, // 明确保留safety_stock字段
        house_areas: houseAreaMap[row.house_id] || []
      }));

      return {
        list: mergedList,
        total: total
      };
    } catch (error) {
      console.error('查询库存记录失败:', error);
      throw error;
    }
  }

  // 获取库存日志
  async getInventoryLog(stock_id) {
    const goods_id = stock_id || null;
    // 改为查询所有 goods_id 相同的记录
    const sql = 'SELECT log_id,stock_id,goods_id,house_id,area_id,change_type,change_quantity,balance,user_id,related_order,change_time,notes,change_reason FROM inventory_log WHERE goods_id = ? ORDER BY log_id DESC';

    try {
      const [result] = await this.db.query(sql, goods_id);
      return result;
    } catch (error) {
      console.error('获取商品入库子表信息失败:', error);
      throw error;
    }
  }


  async getgoodsinfo(goods_id) {
    // 获取商品信息
    const sql = 'SELECT item_id,in_id,goods_id,unit,in_price,in_count,subtotal,house_id,area_id,production_date,expiration_date,supplier_id,is_out,actual_storage,total_remaining,notes,status FROM inventoryinitem WHERE goods_id = ? ORDER BY item_id DESC LIMIT 1';
    try {
      const [result] = await this.db.query(sql, goods_id);
      return result;
    } catch (error) {
      console.error('获取商品信息失败:', error);
      throw error;
    }
  }


  /** 获取仓库信息 */
  async getHouseInfo(house_id) {
    // 获取仓库信息
    const sql = 'SELECT area_id,code,house_id,area_name,storage_type,temp_require,humi_require,status,notes,created_at,updated_at FROM house_area WHERE area_id = ? AND status = 1 ORDER BY area_id DESC LIMIT 1';

    try {
      const [result] = await this.db.query(sql, house_id);
      const houersql = 'SELECT house_id,code,name,type,`condition`,user_id,contact_phone,address,notes,status,created_at,updated_at FROM house WHERE house_id = ? AND status = 1 ORDER BY house_id DESC LIMIT 1';
      const [houseResult] = await this.db.query(houersql, result[0].house_id);
      return {
        ...result[0],
        ...houseResult[0]
      };
    } catch (error) {
      console.error('获取仓库信息失败:', error);
      throw error;
    }
  }
  /** 修改库存信息 */

  async putInventoryInfo(id, params) {
    // 初始化SQL基础部分和参数数组
    let sql = 'UPDATE inventory SET ';
    const queryParams = [];
    const setClauses = [];

    // 动态添加需要更新的字段
    if (params.house_id !== undefined) {
      setClauses.push('house_id = ?');
      queryParams.push(params.house_id);
    }
    if (params.current_stock !== undefined) {
      setClauses.push('current_stock = ?');
      queryParams.push(params.current_stock);
    }
    // if (params.safety_stock !== undefined) {
    
    //   setClauses.push('safety_stock = ?');
    //   queryParams.push(params.safety_stock);
    // }
    if (params.notes !== undefined) {
      setClauses.push('notes = ?');
      queryParams.push(params.notes);
    }

    // 强制更新时间字段（如果需要动态控制可改为if判断）
    const updated_at = dayjs().format('YYYY-MM-DD HH:mm:ss');
    setClauses.push('updated_at = ?');
    queryParams.push(updated_at);

    // 拼接SET子句和WHERE条件
    sql += setClauses.join(', ') + ' WHERE goods_id = ?';
    queryParams.push(id); // 添加WHERE条件的参数

    try {
      const [result] = await this.db.query(sql, queryParams);
      console.log(id);
      return result;
    } catch (error) {
      console.error('修改库存信息失败:', error);
      throw error;
    }
  }


  /** 添加库存日志 */
  async addInventoryLog(params) {
    const stock_id = params.stock_id;
    const goods_id = params.goods_id;
    const house_id = params.house_id;
    const area_id = params.area_id;
    const change_type = params.change_type;
    const change_quantity = params.change_quantity;
    const change_reason = params.change_reason;
    const balance = params.balance;
    const user_id = params.user_id;
    const related_order = params.related_order;
    const notes = params.notes;
    const change_time = dayjs().format('YYYY-MM-DD HH:mm:ss');
    // 添加库存日志
    const sql = 'INSERT INTO inventory_log (stock_id, goods_id, house_id, area_id, change_type, change_reason,change_quantity, balance,user_id, related_order, notes, change_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
    try {
      const [result] = await this.db.query(sql, [stock_id, goods_id, house_id, area_id, change_type, change_reason,change_quantity, balance,user_id, related_order, notes, change_time]);
      return result;
    } catch (error) {
      console.error('添加库存日志失败:', error);
      throw error;
    }
  }
  
  /** 添加库存 */
  async addInventory(params) {
    // 参数验证和类型转换
    const goods_id = parseInt(params.goods_id);
    const house_id = parseInt(params.house_id);
    const current_stock = parseInt(params.current_stock) || 0;
    // const safety_stock = parseInt(params.safety_stock) || 0;
    const status = 0;
    const notes = params.notes || '';
    
    // 验证必需参数
    if (!goods_id || isNaN(goods_id)) {
      throw new Error('商品ID必须是有效的数字');
    }
    
    if (!house_id || isNaN(house_id)) {
      throw new Error('仓库ID必须是有效的数字');
    }
    
    if (isNaN(current_stock) || current_stock < 0) {
      throw new Error('当前库存必须是非负数');
    }
    
    if (isNaN(safety_stock) || safety_stock < 0) {
      throw new Error('安全库存必须是非负数');
    }
    
    console.log('添加库存参数:', {
      goods_id,
      house_id, 
      current_stock,
      // safety_stock,
      status,
      notes
    });
    
    // 添加库存
    const sql = 'INSERT INTO inventory (goods_id, house_id, current_stock, status, notes) VALUES (?, ?, ?, ?, ?)';
    try {
      const [result] = await this.db.query(sql, [goods_id, house_id, current_stock, status, notes]);
      console.log('库存添加成功:', result);
      return result;
    } catch (error) {
      console.error('添加库存失败:', error);
      throw error;
    }
  }
  // 根据仓库的id查找仓库区域
  async getAreaInfo(house_id) {
    // 获取仓库区域信息
    const sql = 'SELECT * FROM house_area WHERE house_id = ? AND status = 1 ';

    try {
      const [result] = await this.db.query(sql, house_id);
      return result;
    } catch (error) {
      console.error('获取仓库区域信息失败:', error);
      throw error;
    }
  }

  // 根据id来查找仓库信息
  async getHouseInfoById(house_id) {
    // 获取仓库信息
    const sql = 'SELECT * FROM house WHERE house_id = ? AND status = 1 ORDER BY house_id DESC LIMIT 1';

    try {
      const [result] = await this.db.query(sql, house_id);
      return result;
    } catch (error) {
      console.error('获取仓库信息失败:', error);
      throw error;
    }
  }

  // 根据商品id获取型号规格当前数量
  async getInventoryByGoodsId(goods_id) {
    // 获取商品型号规格当前数量
    const inventorySql = 'SELECT * FROM inventory WHERE goods_id = ? AND status = 0 ORDER BY stock_id DESC LIMIT 1';
    const goodsSql = 'SELECT * FROM goods WHERE goods_id = ? AND status = 1 ORDER BY goods_id DESC LIMIT 1';
    try {
      const [inventoryResult] = await this.db.query(inventorySql, goods_id);
      const [goodsResult] = await this.db.query(goodsSql, goods_id);
      // 把inventoryResult和goodsResult合并成一个元素
      const mergedResult = {
        ...inventoryResult[0],
        ...goodsResult[0],
      };
      return {
        mergedResult
      };
    } catch (error) {
      console.error('根据商品id获取型号规格当前数量失败:', error);
      throw error;
    }
  }

}

module.exports = InventoryModel;
